SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================
-- Author:		Wagner Silveira
-- Create date: 26/06/2006
-- Description:	Triggers an Notification Event for 
-- Updated and Inserted MessageTypes
-- ===============================================
CREATE TRIGGER dbo.trgMessageTypeSetNotification 
   ON  dbo.MessageType 
   AFTER INSERT, UPDATE
AS 
BEGIN

	set nocount on;

	declare @messagetypeid int
	declare @notifiedServiceProvider varchar(10)
	declare @BatchID bigint;

	select @messagetypeid = isnull(mt.messagetypeid, 0)
	from messagetype mt inner join inserted i
	on mt.messagetypeID = i.messagetypeID
	where mt.[xmlschema] <> i.[xmlschema]

	if (@messagetypeid <> 0)
	begin

		-- Open Event Batch
		exec CN.NSEventBeginBatchMessageTypeChangesData N'ServiceProviderSPEventProvider', @BatchID OUTPUT;

		-- Defines a cursor to get the list of Providers affected by the change.
		declare ServiceProviderList cursor local for
			select distinct serviceproviderid
			from serviceprovider sp
			inner join serviceprovidertypemessagetype sptmt on
			(sptmt.sourceServiceProviderTypeID = sp.ServiceProviderTypeID or sptmt.destinationServiceProviderTypeID = sp.ServiceProviderTypeID)
			where sptmt.messagetypeid = @messagetypeID
			order by serviceproviderid

		open ServiceProviderList

		fetch next from ServiceProviderList into @notifiedServiceProvider

		while @@fetch_status = 0
		begin
			
			-- Write a new event within the Batch
			exec CN.NSEventWriteMessageTypeChangesData @BatchID, @notifiedServiceProvider
			
			fetch next from ServiceProviderList into @notifiedServiceProvider
		end


		close ServiceProviderList
		deallocate ServiceProviderList

		-- Close the Event Batch
		exec CN.NSEventFlushBatchMessageTypeChangesData @BatchID
		
	end

END
GO

